package com.tom;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Driver;

public class Order01DAO {
        public static Connection conn = null;

        public static Connection getConnection() {
                if (conn == null) {
                        try {
                                DriverManager.registerDriver(new Driver());
                                conn = DriverManager.getConnection(
                                                "jdbc:mysql://j.snpy.org/j102u07?useUnicode=true&characterEncoding=UTF-8", "j102u07", "j102u07.989");

                          

                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                        }
                }
                return conn;

        }

        public List<Order01> getAll() {
                List<Order01> order01s = new ArrayList<Order01>();
                try {
                        Statement stmt = getConnection().createStatement();
                        ResultSet rs = stmt.executeQuery("select * from order01");
                        while (rs.next()) {
                                String ord01_num = rs.getString("ord01_num");
                                String ord01_id = rs.getString("ord01_id");
                                String ord01_namec = rs.getString("ord01_namec");
                                String ord01_namea = rs.getString("ord01_namea");
                                String ord01_area = rs.getString("ord01_area");
                                String ord01_telephone = rs.getString("ord01_telephone");
                                String ord01_telephone_p = rs.getString("ord01_telephone_p");
                                String ord01_mobil = rs.getString("ord01_mobil");
                                String ord01_date1 = rs.getString("ord01_date1");
                                String ord01_date2 = rs.getString("ord01_date2");
                                String ord01_pay = rs.getString("ord01_pay");
                                String ord01_sales = rs.getString("ord01_sales");
                                String ord01_yn = rs.getString("ord01_yn");
                                String ord01_date3 = rs.getString("ord01_date3");
                                String ord01_memo = rs.getString("ord01_memo");                             
                                Order01 order01 = new Order01(ord01_num, ord01_id, ord01_namec, ord01_namea, ord01_area, ord01_telephone, ord01_telephone_p, ord01_mobil, ord01_date1, ord01_date2,ord01_pay, ord01_sales, ord01_yn, ord01_date3, ord01_memo);
                                order01s.add(order01);
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return order01s;
        }

        public int delete(String ord01_num) {
                String sql = "delete from order01 where ord01_num=?";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, ord01_num);
                        rowCount = pstmt.executeUpdate();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }
        
        public int add(Order01 o){
                String sql = "insert into order01(ord01_num,ord01_id,ord01_namec,ord01_namea,ord01_area,ord01_telephone,ord01_telephone_p,ord01_mobil,ord01_date1,ord01_date2,ord01_pay,ord01_sales,ord01_yn,ord01_date3,ord01_memo) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        
                        pstmt.setString(1, o.getOrd01_num());
                        pstmt.setString(2, o.getOrd01_id());
                        pstmt.setString(3, o.getOrd01_namec());
                        pstmt.setString(4, o.getOrd01_namea());
                        pstmt.setString(5, o.getOrd01_area());
                        pstmt.setString(6, o.getOrd01_telephone());
                        pstmt.setString(7, o.getOrd01_telephone_p());
                        pstmt.setString(8, o.getOrd01_mobil());
                        pstmt.setString(9, o.getOrd01_date1());
                        pstmt.setString(10, o.getOrd01_date2());                  
                        pstmt.setString(11, o.getOrd01_pay());
                        pstmt.setString(12, o.getOrd01_sales());
                        pstmt.setString(13, o.getOrd01_yn());
                        pstmt.setString(14, o.getOrd01_date3());
                        pstmt.setString(15, o.getOrd01_memo());
                        rowCount = pstmt.executeUpdate();
                        System.out.println(rowCount);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }
        
        public int update(Order01 o){
                String sql = "update order01 set ord01_id=?,ord01_namec=?,ord01_namea=?,ord01_area=?,ord01_telephone=?,ord01_telephone_p=?,ord01_mobil=?,ord01_date1=?,ord01_date2=?,ord01_pay=?,ord01_sales=?,ord01_yn=?,ord01_date3=?,ord01_memo=?where ord01_num=?";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, o.getOrd01_id());
                        pstmt.setString(2, o.getOrd01_namec());
                        pstmt.setString(3, o.getOrd01_namea());
                        pstmt.setString(4, o.getOrd01_area());
                        pstmt.setString(5, o.getOrd01_telephone());
                        pstmt.setString(6, o.getOrd01_telephone_p());
                        pstmt.setString(7, o.getOrd01_mobil());
                        pstmt.setString(8, o.getOrd01_date1());
                        pstmt.setString(9, o.getOrd01_date2());
                        pstmt.setString(10, o.getOrd01_pay());
                        pstmt.setString(11, o.getOrd01_sales());
                        pstmt.setString(12, o.getOrd01_yn());
                        pstmt.setString(13, o.getOrd01_date3());
                        pstmt.setString(14, o.getOrd01_memo());
                        pstmt.setString(15, o.getOrd01_num());
                        rowCount = pstmt.executeUpdate();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }

        public Order01 getOrder01ById(String ord01_num ){
                Order01 order01 = null;
                String sql = "select * from order01 where ord01_num=?";
               
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, ord01_num);
                        ResultSet rs = pstmt.executeQuery();
                        if (rs.next()){
                                order01 = new Order01(rs.getString("ord01_num")
                                                , rs.getString("ord01_id")
                                                , rs.getString("ord01_namec")
                                                , rs.getString("ord01_namea")
                                                , rs.getString("ord01_area")
                                                , rs.getString("ord01_telephone")
                                                , rs.getString("ord01_telephone_p")
                                                , rs.getString("ord01_mobil")
                                                , rs.getString("ord01_date1")
                                                , rs.getString("ord01_date2")
                                                , rs.getString("ord01_pay")
                                                , rs.getString("ord01_sales")
                                                , rs.getString("ord01_yn")
                                                , rs.getString("ord01_date3")
                                                , rs.getString("ord01_memo"));
                                                
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return order01;
        }
  /*      
        public static void main(String[] args){
//              testgetAll();
                Order01DAO dao = new Order01DAO();
                System.out.println(dao.delete("aa123"));
        }
        */

     /*   private static void testgetAll() {
        	Order01DAO dao = new Order01DAO();
                List<Order01> list = dao.getAll();
                System.out.println(list.size());
        }
        */
}